1. To-Increase Dynamics 365 for Finance & Operations
  2. Data modeling studio
  3. Setting up and maintaining Data modeling studio
  4. Set up and maintain data models

Set up data models

In the Data modeling studio, a data model defines the data to be exported. For each data model, you define the applicable data sets or data entities.

For each data set, that is added to a data model, you can define which of the data set fields are exported.


DMS Administrator DMS Administrator Start Start Create data model Create data model In the Data modeling studio, a data model defines the data to be exported, how to export the data, where to export the data to, and when to export the data. For each data model, define these settings: Identification: The identification of the data model. Model: How the data is exported and how the target database is configured. Destination: The schema that is used to create the table in the target database on deploy. Metadata: Which metadata is included added to the target database on deploy. Scheduling: How the data export is scheduled for the data model. Update information: The contact info of the data model owner. Connection: The connection to the target database. Procedure 1. Go to Data modeling studio > Data modeling studio > Data models. 2. Click New. 3. Sub-task: Define identification settings. 4. In the Name field, type a value. 5. In the Description field, type a value. 6. Sub-task: Define model settings. 7. In the Mode field, select 'Unmanaged'. 8. In the Model type field, select an option. 9. Sub-task: Define connection string. 10. Enter the connection to the target database. The recommended structure for the connection string is: Data Source=...;Initial Catalog=...;Integrated Security=...;User ID=...;Password=... In the Connection string field, type a value. Note: The account, as defined in the connection string, must have owner privileges in the target database. For example, to be able to: - Export data to the target database. - Create tables in the target database. - Create objects in the target database using automation scripts (transformations). 11. Click Show. 12. Click Test. 13. Sub-task: Define destination settings. 14. Enter or select the schema that is applied to create the tables in the target database. To select a schema, the connection to the target database must be defined and tested successfully. You can select a schema as defined for the target database. You can also manually enter a schema. If the schema does not exist in the target database, it is created automatically when the data model is deployed. In the Schema field, enter or select a value. Note: - The default schema is defined in the Data modeling studio parameters, on the Data export tab, in the Schema field. - In an ETL (Extract, Transform, Load) process, the best practice schema for unprocessed data is 'RAW'. 15. Sub-task: Define metadata settings. 16. Select the desired option in the Application metadata field. 17. You can add processing metadata tables to the target database. If you select 'Yes', on deploy, these tables are created and filled in the target database: - CDPLogStaging: This table is used to log the deploy and export history. It shows the export history using messages. - CDPProcessingStaging: This table is used to log the deploy and export processing history. It shows the technical details of the export history. On data export, the data in these tables is updated. Select the desired option in the Processing metadata field. Note: You can view the log and processing history on the Data exchange monitoring page. 18. Select the desired option in the Data model metadata field. 19. Sub-task: Define schedule settings. 20. Select the desired option in the Schedule by export group field. 21. Select the desired option in the Schedule full load field. 22. Sub-task: Define update information settings. 23. In the Contact field, type a value. Use export groups? Use export groups? Create export groups for data model Create export groups for data model You can enable a data model export to be scheduled by export group. To export by export group, for each data set or data entity, as added to the data model, define the applicable export group. On export, the data sets and data entities with the same export group defined, are combined in one batch job. If you schedule export by export group, for one data model, you can schedule export for several data subsets in a different recurrence. Export groups are defined by data model. To be able to use export group, create the export groups for the data model. Procedure 1. Go to Data modeling studio > Data modeling studio > Data models. 2. In the list, click the link of the desired data model. 3. Expand the Data sets and data entities section. 4. Click Export groups. 5. On the dialog, click Add. 6. In the Export group field, type a value. 7. Click OK. Add data set or data entity to data model Add data set or data entity to data model In the Data modeling studio, a data model defines the data to be exported. For each data model, you define the applicable: Data sets: You can add the data sets as defined in the Data modeling studio. An added data set gets the mode 'Unmanaged'. Data entities: You can add standard or custom data entities as available for D365 FO. An added data entity gets the mode 'Managed'. Procedure 1. Go to Data modeling studio > Data modeling studio > Data models. 2. In the list, click the link of the desired data model. 3. Click Edit. 4. Expand the Data sets and data entities section. 5. Sub-task: Add several data sets and data entities. 6. Click Add multiple.... 7. Click 'Show only unmanaged' or 'Show all'. 8. In the list, find and select the desired data sets and data entities. Note: To distinguish data sets and data entities, in the list, view the AOT name field. If this name ends with '...Entity', it is a data entity. Otherwise, it is a dataset. 9. Click OK. 10. Sub-task: Add one data set or data entity. 11. Click Add. 12. In the Entity field, enter or select the desired data set or data entity. Note: To distinguish data sets and data entities, in the lookup, view the AOT name field. If this name ends with '...Entity', it is a data entity. Otherwise, it is a dataset. 13. In the Export group field, enter or select a value. 14. Sub-task: Define data set/entity-specific transformation. 15. You can define a transformation for each data set or data entity as added to the data model. Use the transformation to do calculations based on the data that has just been exported to the target database. The transformation is executed directly after the export for the data set or data entity is finished. For the desired data set or data entity, in the Processing field, click the '+' icon. Note: If already a transformation is defined, click the Edit icon. 16. In the Templates field, enter or select the desired transformation. Note: Only the transformations of category 'Processing' are shown in the list. 17. Click OK. Apply template to data model Apply template to data model You can use a template to easily add data sets and data entities to a data model. You can use templates that are created from the: Data models page. Before you apply a template that is created from the Data models page, make sure that the data sets and data entities, as saved in the template, already exist in your D365 FO environment. Data sets page. If a data set in the template: Does not exist, the data set is created with the settings and fields as defined in the template. Also, the data set is added to the data model. Already exists and the template has (several) other fields than the data set, these fields are added to the existing data set. The existing data set fields that are not in the template are not removed from the data set. So, you can only use a template to add fields to a data set and not to remove fields from a data set. Procedure 1. Go to Data modeling studio > Data modeling studio > Data models. 2. In the list, click the link of the desired data model. 3. Expand the Dataset section. 4. Click Open template. 5. On the dialog, select the desired option in the Overwrite General fields of existing data set field. 6. In the list, find and select the desired templates. 7. Click OK. Notes If you apply a data set template that changes an existing data set, you must redeploy the data set. You can also redeploy the related data models. Select data set  fields to be exported? Select data set  fields to be exported? Select fields Select fields If you add a data set to a data model, by default all data set fields are included in the data export. For a data model, you can select a subset of the data set fields to be included in the data export. Note: If a data set is used in a data model, you can still add fields to the data set. These newly added data set fields are not added automatically to the selected fields for the data model. So, if fields are added to a data set, and these must be included in the data export, manually select these fields for the data model. Procedure 1. Go to Data modeling studio > Data modeling studio > Data models. 2. In the list, click the link in the desired data model. 3. Click Edit. 4. Expand the Data sets and data entities section. 5. In the list, for the desired data set, in the Fields field, click the 'filter' or 'edit' icon. Note: The icon differs based on the selection: - If all fields are selected, the 'filter' icon is shown. - If not all fields are selected, the 'edit' icon is shown. 6. On the dialog, in the fields list, clear or select the 'Selected' check box for each field, as desired. Note: - You can also use the options to select or deselect all fields at once. - You cannot deselect fields that are marked as index field on the applicable data set. 7. Click OK. Update metadata Update metadata In Data modeling studio, several metadata tables exist, which are used: On deploy. On deploy or export. In data modeling. For the Data modeling studio metadata tables, the data is collected in D365 FO and stored in these metadata tables in D365 FO. On deploy or export, for some of these metadata tables, the data is taken from these metadata tables in D365 FO. For other metadata tables, the data is used in data modeling. It is important to keep the data in the metadata tables up-to-date. Make sure to update the metadata tables after each creation or change of a data model. Procedure 1. Go to Data modeling studio > Data modeling studio > Parameters. 2. Click the Metadata synchronization tab. 3. Click Update metadata. 4. Close the page. End End Yes No Yes No

Activities

Name Responsible Description

Create data model

DMS Administrator

In the Data modeling studio, a data model defines the data to be exported, how to export the data, where to export the data to, and when to export the data.

For each data model, define these settings:

  • Identification: The identification of the data model.
  • Model: How the data is exported and how the target database is configured.
  • Destination: The schema that is used to create the table in the target database on deploy.
  • Metadata: Which metadata is included added to the target database on deploy.
  • Scheduling: How the data export is scheduled for the data model.
  • Update information: The contact info of the data model owner.
  • Connection: The connection to the target database.

Create export groups for data model

DMS Administrator

You can enable a data model export to be scheduled by export group. To export by export group, for each data set or data entity, as added to the data model, define the applicable export group.
On export, the data sets and data entities with the same export group defined, are combined in one batch job.
If you schedule export by export group, for one data model, you can schedule export for several data subsets in a different recurrence.

Export groups are defined by data model. To be able to use export group, create the export groups for the data model.

Add data set or data entity to data model

DMS Administrator

In the Data modeling studio, a data model defines the data to be exported. For each data model, you define the applicable:

  • Data sets: You can add the data sets as defined in the Data modeling studio. An added data set gets the mode 'Unmanaged'.
  • Data entities: You can add standard or custom data entities as available for D365 FO. An added data entity gets the mode 'Managed'.

Apply template to data model

DMS Administrator

You can use a template to easily add data sets and data entities to a data model.

You can use templates that are created from the:

  • Data models page. Before you apply a template that is created from the Data models page, make sure that the data sets and data entities, as saved in the template, already exist in your D365 FO environment.
  • Data sets page. If a data set in the template:
    • Does not exist, the data set is created with the settings and fields as defined in the template. Also, the data set is added to the data model.
    • Already exists and the template has (several) other fields than the data set, these fields are added to the existing data set. The existing data set fields that are not in the template are not removed from the data set. So, you can only use a template to add fields to a data set and not to remove fields from a data set.

Select fields

DMS Administrator

If you add a data set to a data model, by default all data set fields are included in the data export. For a data model, you can select a subset of the data set fields to be included in the data export.

Note: If a data set is used in a data model, you can still add fields to the data set. These newly added data set fields are not added automatically to the selected fields for the data model. So, if fields are added to a data set, and these must be included in the data export, manually select these fields for the data model.

Update metadata

DMS Administrator

In Data modeling studio, several metadata tables exist, which are used:

  • On deploy.
  • On deploy or export.
  • In data modeling.

For the Data modeling studio metadata tables, the data is collected in D365 FO and stored in these metadata tables in D365 FO.
On deploy or export, for some of these metadata tables, the data is taken from these metadata tables in D365 FO. For other metadata tables, the data is used in data modeling.

It is important to keep the data in the metadata tables up-to-date. Make sure to update the metadata tables after each creation or change of a data model.

See also

Provide feedback